package classes;

import DBConnection.ConnectionFactory;
import java.io.File;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.StringTokenizer;
import java.util.Vector;
import javax.swing.JFileChooser;
import javax.swing.JOptionPane;
import javax.swing.table.DefaultTableModel;
import jxl.Workbook;
import jxl.write.*;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 *
 * @author Ervin  James
 */
public class ReportGeneration {
    
    public Vector ViewReport(String date1, String date2)
    {
         Vector serviceList = new Vector<String>();
          
        try{
            ConnectionFactory myFactory = ConnectionFactory.getFactory();
            Connection conn = myFactory.getConnection();

            int i = 1;

            PreparedStatement pstmt = conn.prepareStatement("SELECT Date, (SELECT ServiceName from servicecategory WHERE servicecategory.CategoryID = servicerendered.CategoryID), Description, CONCAT((SELECT PatientLName from patientlist WHERE patientlist.PatientID = servicerendered.PatientID), ', ' ,(SELECT PatientFName from patientlist WHERE patientlist.PatientID = servicerendered.PatientID), ' ', (SELECT PatientMName from patientlist WHERE patientlist.PatientID = servicerendered.PatientID)) AS FullName, Price FROM servicerendered WHERE Date BETWEEN ? AND ? ORDER BY 1"); 
            pstmt.setString(i++, date1);
            pstmt.setString(i++, date2);
            ResultSet rs = pstmt.executeQuery();
            
            ResultSetMetaData rsmd = (ResultSetMetaData) rs.getMetaData();
            int colNo = rsmd.getColumnCount();
         
            while(rs.next())
            {
                Vector service = new Vector();
                
                for(int j=0;j<colNo;j++){
                    service.add(rs.getString(j+1));
                    }        
               serviceList.add(service);
     
            }
           
            
            conn.close();
        }
        catch(SQLException e)
        {
           e.printStackTrace();
        }

        return serviceList;
    }
    
    public Vector ViewReportPer(String date1, String date2, boolean flag) {
        
        Vector serviceList = new Vector<String>();
          
        try{
            ConnectionFactory myFactory = ConnectionFactory.getFactory();
            Connection conn = myFactory.getConnection();

            int i = 1;
            
            PreparedStatement pstmt;
            
            if(flag == true)
                pstmt = conn.prepareStatement("SELECT (SELECT ServiceName FROM servicecategory WHERE servicecategory.CategoryID = servicerendered.CategoryID), SUM(Price) FROM servicerendered WHERE Date BETWEEN ? AND ? GROUP BY CategoryID");
            else 
                pstmt = conn.prepareStatement("SELECT (SELECT ServiceName FROM servicecategory WHERE servicecategory.CategoryID = servicerendered.CategoryID), COUNT(*) FROM servicerendered WHERE Date BETWEEN ? AND ? GROUP BY CategoryID");
            
            pstmt.setString(i++, date1);
            pstmt.setString(i++, date2);
            ResultSet rs = pstmt.executeQuery();
            
            ResultSetMetaData rsmd = (ResultSetMetaData) rs.getMetaData();
            int colNo = rsmd.getColumnCount();
         
            while(rs.next())
            {
                Vector service = new Vector();
                
                for(int j=0;j<colNo;j++){
                    service.add(rs.getString(j+1));
                    }        
               serviceList.add(service);
     
            }
           
            
            conn.close();
        }
        catch(SQLException e)
        {
           e.printStackTrace();
        }

        return serviceList;
    }
    
    public String GetTotal(String date1, String date2, boolean flag)
    {
         String sum = new String();
          
        try{
            ConnectionFactory myFactory = ConnectionFactory.getFactory();
            Connection conn = myFactory.getConnection();

            int i = 1;

            PreparedStatement pstmt;
            if(flag==true)
               pstmt = conn.prepareStatement("SELECT SUM(Price) FROM servicerendered WHERE Date BETWEEN ? AND ? ORDER BY 1"); 
            else
              pstmt = conn.prepareStatement("SELECT COUNT(*) FROM servicerendered WHERE Date BETWEEN ? AND ? ORDER BY 1");  
            pstmt.setString(i++, date1);
            pstmt.setString(i++, date2);
            ResultSet rs = pstmt.executeQuery();
            
            ResultSetMetaData rsmd = (ResultSetMetaData) rs.getMetaData();
            int colNo = rsmd.getColumnCount();
         
            while(rs.next())
            {
               sum = rs.getString(1);
     
            }
           
            
            conn.close();
        }
        catch(SQLException e)
        {
           e.printStackTrace();
        }

        return sum;
    }
    
    public void GenerateExcelReport(DefaultTableModel dtm1, DefaultTableModel dtm2, String Total, boolean flag)
    {
        try {
            JFileChooser chooser = new JFileChooser(System.getProperty("user.dir"));
            File f = new File("report.xls");
            chooser.setSelectedFile(f);
            
            int option = chooser.showSaveDialog(Main.frame.report);  
           
            if(option == JFileChooser.APPROVE_OPTION){  
            if(chooser.getSelectedFile()!=null){  
                File theFileToSave = chooser.getSelectedFile();  
                WritableWorkbook workbook = Workbook.createWorkbook(theFileToSave);
            
            WritableSheet sheet = workbook.createSheet("First Sheet", 0); 
            
            WritableFont arial16font = new WritableFont(WritableFont.ARIAL, 16, WritableFont.BOLD, true);
            WritableCellFormat arial16format = new WritableCellFormat (arial16font); 
            
            WritableFont arial10font = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, true);
            WritableCellFormat arial10format = new WritableCellFormat (arial10font); 
            Label label;
            if(flag==true)
                label = new Label(0, 0, "Sales Report", arial16format);
            else 
                label = new Label(0, 0, "Services Report", arial16format);
            sheet.addCell(label);
            Label label2 = new Label(0, 2, "Date", arial10format);
            sheet.addCell(label2);
            Label label3 = new Label(1, 2, "Service Category", arial10format);
            sheet.addCell(label3);
            Label label4 = new Label(2, 2, "Description", arial10format);
            sheet.addCell(label4);
            Label label5 = new Label(3, 2, "Patient Name", arial10format);
            sheet.addCell(label5);
            Label label6 = new Label(4, 2, "Price", arial10format);
            sheet.addCell(label6);
            
            int k =0; 
            int i = 0;
            int j = 0;
            int h = 0;
            
            for(k=0; k < dtm1.getColumnCount();k++)
            {
                for(i=0; i < dtm1.getRowCount();i++)
                {

                          sheet.addCell(new Label(k,i+3,dtm1.getValueAt(i, k).toString()));
                        
                }
            }
            
             Label label7 = new Label(0, i+4, "Service Category", arial10format);
             sheet.addCell(label7);
             Label label8 = new Label(1, i+4, "Price", arial10format);
             sheet.addCell(label8);
            
             for(j=0; j < dtm2.getColumnCount();j++)
            {
                for(h=0; h < dtm2.getRowCount();h++)
                {

                          sheet.addCell(new Label(j,h+i+5,dtm2.getValueAt(h, j).toString()));
                          
                }
            }
            Label label9 = new Label(0, h+i+7, "Total", arial10format); 
            sheet.addCell(label9);
            StringTokenizer token = new StringTokenizer(Total, "P");
            while(token.hasMoreTokens())
            {
                Total = token.nextToken();
            }
            Label label10 = new Label(1, h+i+7, Total, arial10format); 
            sheet.addCell(label10);
            
            workbook.write();
            workbook.close();
            JOptionPane.showMessageDialog(Main.frame.report, "Excel Report Generation Successful!", "Message", JOptionPane.INFORMATION_MESSAGE);
            }
        } 
        }catch (FileNotFoundException e) {
            JOptionPane.showMessageDialog(Main.frame.report,"Make sure that the document 'output.xls' is not open in another program.", "Error Message", JOptionPane.ERROR_MESSAGE);
        }
          catch(NullPointerException e) {
            JOptionPane.showMessageDialog(Main.frame.report,"Please generate a report that is not empty first.", "Error Message", JOptionPane.ERROR_MESSAGE);  
          }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }
    
}
